﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for productDAL
/// </summary>
public class productDAL
{
    DataAccess da = new DataAccess();
	public productDAL()
	{
		//
		// TODO: Add constructor logic here
		//
       

	}

    public int insertProduct(product product)
    {

        SqlParameter[] paramList = new SqlParameter[17];
        paramList[1] = da.AddParameter("product_name", product.Product_name, SqlDbType.NVarChar, 50);
        paramList[2] = da.AddParameter("product_des", product.Product_des, SqlDbType.NVarChar);
        paramList[3] = da.AddParameter("product_quantity", product.Product_quantity, SqlDbType.Int);
        paramList[4] = da.AddParameter("unit_id", product.Unit_id, SqlDbType.Int);
        paramList[5] = da.AddParameter("image", product.Image, SqlDbType.Text);
        paramList[6] = da.AddParameter("manufacture_id", product.Manufacturer_id, SqlDbType.Int);   
        paramList[7] = da.AddParameter("price", product.Price, SqlDbType.Int);
        paramList[8] = da.AddParameter("date_available", product.Date_available, SqlDbType.DateTime);
        return da.executeScalary("IS_Product", paramList);

    }

    public int deleteProduct(product product){
        SqlParameter[] paramList = new SqlParameter[1];
        paramList[0] = da.AddParameter("product_id", product.Product_id, SqlDbType.Int);
        return da.executeUpdate("DE_Product", paramList);
    }

  
    public DataTable getAllProducts(){
        SqlParameter[] paramList = new SqlParameter[8];
        paramList[0] = da.AddParameter("product_id", DBNull.Value, SqlDbType.Int);
        paramList[1] = da.AddParameter("product_name", DBNull.Value, SqlDbType.NVarChar);
        paramList[3] = da.AddParameter("manufacturer_id", DBNull.Value, SqlDbType.Int);
        paramList[2] = da.AddParameter("product_quantity", DBNull.Value, SqlDbType.Int);
        paramList[4] = da.AddParameter("price", DBNull.Value, SqlDbType.Int);
        paramList[5] = da.AddParameter("date_available", DBNull.Value, SqlDbType.NVarChar);
        paramList[6] = da.AddParameter("status", DBNull.Value, SqlDbType.Bit);
        paramList[7] = da.AddParameter("category_id", DBNull.Value, SqlDbType.Int);
        return da.executeSelect("Select_Product",paramList);
    }
    public DataTable searchProduct(string product_name)
    {
        SqlParameter[] paramList = new SqlParameter[1];
        paramList[0] = da.AddParameter("product_name", product_name, SqlDbType.NVarChar, 32);
        return da.executeSelect("Search_Product", paramList);
    }
    public DataTable getProductByID(int product_id)
    {
        SqlParameter[] paramList = new SqlParameter[1];
        paramList[0] = da.AddParameter("product_id", product_id,SqlDbType.Int);        
        
        return da.executeSelect("Select_Product", paramList);
    }
    public DataTable getProductRelatedByID(int product_id, int category_id)
    {
        SqlParameter[] paramList = new SqlParameter[2];
        paramList[0] = da.AddParameter("product_id", product_id, SqlDbType.Int);
        paramList[1] = da.AddParameter("category_id", category_id, SqlDbType.Int);

        return da.executeSelect("Select_Product", paramList);
    }
    public DataTable getProductsByCategoryId(int category_id)
    {
        SqlParameter[]  paramList = new SqlParameter[8];
        paramList[0] = da.AddParameter("product_id", DBNull.Value, SqlDbType.Int);
        paramList[1] = da.AddParameter("product_name", DBNull.Value, SqlDbType.NVarChar);
        paramList[3] = da.AddParameter("manufacturer_id", DBNull.Value, SqlDbType.Int);
        paramList[2] = da.AddParameter("product_quantity", DBNull.Value, SqlDbType.Int);
        paramList[4] = da.AddParameter("price", DBNull.Value, SqlDbType.Int);
        paramList[5] = da.AddParameter("date_available", DBNull.Value, SqlDbType.NVarChar);
        paramList[6] = da.AddParameter("status", DBNull.Value, SqlDbType.Bit);
        paramList[7] = da.AddParameter("category_id", category_id, SqlDbType.Int);
        return da.executeSelect("Select_Product", paramList);
    }
    public DataTable getAllProductsByManufactureId(int manufacturer_id)
    {
        SqlParameter[] paramList = new SqlParameter[8];
        paramList[3] = da.AddParameter("manufacturer_id", manufacturer_id, SqlDbType.Int);
        paramList[6] = da.AddParameter("status", true, SqlDbType.Bit);
        return da.executeSelect("Select_Product", paramList);
    }
    public DataTable getProductsByName(string product_name)
    {
        SqlParameter[] paramList = new SqlParameter[2];
        paramList[0] = da.AddParameter("product_id", DBNull.Value, SqlDbType.Int);
        paramList[1] = da.AddParameter("product_name", product_name, SqlDbType.NVarChar, 32);
        return da.executeSelect("Select_Product", paramList);
    }
    public DataTable getProductsByNameACategoryID(string product_name, int category_id)
    {
        SqlParameter[] paramList = new SqlParameter[8];
        paramList[0] = da.AddParameter("product_id", DBNull.Value, SqlDbType.Int);
        paramList[1] = da.AddParameter("product_name", product_name, SqlDbType.NVarChar, 32);
        paramList[2] = da.AddParameter("product_quantity", DBNull.Value, SqlDbType.Int);
        paramList[3] = da.AddParameter("manufacturer_id", DBNull.Value, SqlDbType.Int);
        paramList[4] = da.AddParameter("price", DBNull.Value, SqlDbType.Int);
        paramList[5] = da.AddParameter("date_available", DBNull.Value, SqlDbType.DateTime);
        paramList[6] = da.AddParameter("status", DBNull.Value, SqlDbType.Bit);
        paramList[7] = da.AddParameter("category_id", category_id, SqlDbType.Int);
        return da.executeSelect("Select_Product", paramList);
    }
    public DataTable getProductsByNameACategoryIDAPrice(string product_name, int category_id, int price)
    {
        SqlParameter[] paramList = new SqlParameter[8];
        paramList[0] = da.AddParameter("product_id", DBNull.Value, SqlDbType.Int);
        paramList[1] = da.AddParameter("product_name", product_name, SqlDbType.NVarChar, 32);
        paramList[2] = da.AddParameter("product_quantity", DBNull.Value, SqlDbType.Int);
        paramList[3] = da.AddParameter("manufacturer_id", DBNull.Value, SqlDbType.Int);
        paramList[4] = da.AddParameter("price", price, SqlDbType.Int);
        paramList[5] = da.AddParameter("date_available", DBNull.Value, SqlDbType.DateTime);
        paramList[6] = da.AddParameter("status", DBNull.Value, SqlDbType.Bit);
        paramList[7] = da.AddParameter("category_id", category_id, SqlDbType.Int);
        return da.executeSelect("Select_Product", paramList);
    }
    public DataTable getProductsByDes(string product_des)
    {
        SqlParameter[] paramList = new SqlParameter[9];
        paramList[0] = da.AddParameter("product_id", DBNull.Value, SqlDbType.Int);
        paramList[1] = da.AddParameter("product_name", DBNull.Value, SqlDbType.NVarChar, 32);
        paramList[2] = da.AddParameter("product_quantity", DBNull.Value, SqlDbType.Int);
        paramList[3] = da.AddParameter("manufacturer_id", DBNull.Value, SqlDbType.Int);
        paramList[4] = da.AddParameter("price", DBNull.Value, SqlDbType.Int);
        paramList[5] = da.AddParameter("date_available", DBNull.Value, SqlDbType.DateTime);
        paramList[6] = da.AddParameter("status", DBNull.Value, SqlDbType.Bit);
        paramList[7] = da.AddParameter("category_id", DBNull.Value, SqlDbType.Int);
        paramList[8] = da.AddParameter("product_des", product_des, SqlDbType.NVarChar, 255);
        return da.executeSelect("Select_Product", paramList);
    }
    public DataTable getProductsByDesACategory(string product_des, int category_id)
    {
        SqlParameter[] paramList = new SqlParameter[9];
        paramList[0] = da.AddParameter("product_id", DBNull.Value, SqlDbType.Int);
        paramList[1] = da.AddParameter("product_name", DBNull.Value, SqlDbType.NVarChar, 32);
        paramList[2] = da.AddParameter("product_quantity", DBNull.Value, SqlDbType.Int);
        paramList[3] = da.AddParameter("manufacturer_id", DBNull.Value, SqlDbType.Int);
        paramList[4] = da.AddParameter("price", DBNull.Value, SqlDbType.Int);
        paramList[5] = da.AddParameter("date_available", DBNull.Value, SqlDbType.DateTime);
        paramList[6] = da.AddParameter("status", DBNull.Value, SqlDbType.Bit);
        paramList[7] = da.AddParameter("category_id", category_id, SqlDbType.Int);
        paramList[8] = da.AddParameter("product_des", product_des, SqlDbType.NVarChar, 255);
        return da.executeSelect("Select_Product", paramList);
    }
    public DataTable getProductsByDesACategoryAPrice(string product_des, int category_id, int price)
    {
        SqlParameter[] paramList = new SqlParameter[9];
        paramList[0] = da.AddParameter("product_id", DBNull.Value, SqlDbType.Int);
        paramList[1] = da.AddParameter("product_name", DBNull.Value, SqlDbType.NVarChar, 32);
        paramList[2] = da.AddParameter("product_quantity", DBNull.Value, SqlDbType.Int);
        paramList[3] = da.AddParameter("manufacturer_id", DBNull.Value, SqlDbType.Int);
        paramList[4] = da.AddParameter("price", price, SqlDbType.Int);
        paramList[5] = da.AddParameter("date_available", DBNull.Value, SqlDbType.DateTime);
        paramList[6] = da.AddParameter("status", DBNull.Value, SqlDbType.Bit);
        paramList[7] = da.AddParameter("category_id", category_id, SqlDbType.Int);
        paramList[8] = da.AddParameter("product_des", product_des, SqlDbType.NVarChar, 255);
        return da.executeSelect("Select_Product", paramList);
    }
    public int updateViewbyProductID(int product_id){
        SqlParameter[] paramList = new SqlParameter[1];
        paramList[0] = da.AddParameter("product_id", product_id, SqlDbType.Int);
        return da.executeUpdate("Update_viewed", paramList);
    }

    public int refreshViewbyProductID()
    {
        SqlParameter[] paramList = new SqlParameter[1];
        paramList[0] = da.AddParameter("product_id", DBNull.Value, SqlDbType.Int);
        return da.executeUpdate("Update_viewed", paramList);
    }

    public int UpdateOrderByProductID(int product_id)
    {
        SqlParameter[] paramList = new SqlParameter[1];
        paramList[0] = da.AddParameter("product_id", product_id, SqlDbType.Int);
        return da.executeUpdate("Update_ProductOrder", paramList);
    }

    public int RefreshOrderByProductID()
    {
        SqlParameter[] paramList = new SqlParameter[1];
        paramList[0] = da.AddParameter("product_id", DBNull.Value, SqlDbType.Int);
        return da.executeUpdate("Update_ProductOrder", paramList);
    }

    public DataTable getHotProduct(int quantity){
        SqlParameter[] paramList = new SqlParameter[1];
        paramList[0] = da.AddParameter("quantity", quantity, SqlDbType.Int);
        return da.executeSelect("SELECT_HOT", paramList);
    }
    public DataTable getBestSeller(int quantity){
        SqlParameter[] paramList = new SqlParameter[1];
        paramList[0] = da.AddParameter("quantity", quantity, SqlDbType.Int);
        return da.executeSelect("SELECT_BESTSELLER", paramList);
    }

    //select product for admin
    public DataTable getProductForAdminDAL()
    {

        DataTable dt = new DataTable();
        dt = da.executeSelect("sp_SelectProduct");
        return dt;
    }

    //Insert Product
    public int InsertProcductDAL(product p)
    {
        SqlParameter[] paramList = new SqlParameter[8];
        paramList[0] = da.AddParameter("product_name", p.Product_name, SqlDbType.NVarChar);
        paramList[1] = da.AddParameter("product_des", p.Product_des, SqlDbType.NVarChar);
        paramList[2] = da.AddParameter("product_quantity", p.Product_quantity, SqlDbType.Int);
        paramList[3] = da.AddParameter("unit_id", p.Unit_id, SqlDbType.Int);
        paramList[4] = da.AddParameter("image", p.Image, SqlDbType.NVarChar);
        paramList[5] = da.AddParameter("manufacturer_id", p.Manufacturer_id, SqlDbType.Int);
        paramList[6] = da.AddParameter("price", p.Price, SqlDbType.Int);
        paramList[7] = da.AddParameter("status", p.Status, SqlDbType.Bit);
        return da.executeUpdate("sp_InsertProduct", paramList);
    }

    //Update product
    public int UpdateProductDAL(product p)
    {
        SqlParameter[] paramList = new SqlParameter[13];
        paramList[0] = da.AddParameter("product_id", p.Product_id, SqlDbType.Int);
        paramList[1] = da.AddParameter("product_name", p.Product_name, SqlDbType.NVarChar);
        paramList[2] = da.AddParameter("product_des", p.Product_des, SqlDbType.NVarChar);
        paramList[3] = da.AddParameter("product_quantity", p.Product_quantity, SqlDbType.Int);
        paramList[4] = da.AddParameter("unit_id", p.Unit_id, SqlDbType.Int);
        paramList[5] = da.AddParameter("image", p.Image, SqlDbType.NVarChar);
        paramList[6] = da.AddParameter("manufacturer_id", p.Manufacturer_id, SqlDbType.Int);
        paramList[7] = da.AddParameter("date_available", DBNull.Value, SqlDbType.DateTime);
        paramList[8] = da.AddParameter("price", p.Price, SqlDbType.Int);
        paramList[9] = da.AddParameter("status", p.Status, SqlDbType.Bit);
        paramList[10] = da.AddParameter("date_added", p.Date_added, SqlDbType.DateTime);
        paramList[11] = da.AddParameter("viewed", p.Viewed, SqlDbType.Int);
        paramList[12] = da.AddParameter("sort_order", p.Sort_Order, SqlDbType.Int);
        return da.executeUpdate("sp_UpdateProduct", paramList);
    }
}